﻿using BwTMusicModels;
using HelperModels.Songs;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BwTServices.Song
{
    public class SongServices
    {
        public List<SongHelper> GetAllSongsList(SongFilter filter,out int total)
        {
            List<SongHelper> Lists = new List<SongHelper>();
            total = 0;
            try
            {
                if (filter != null)
                {
                    if (filter.PageSize > 0 && filter.CurrentPage > 0)
                    {
                        StringBuilder sbSQL = new StringBuilder();
                        List<SqlParameter> listParam = new List<SqlParameter>();
                        sbSQL.Append("select top ");
                        sbSQL.Append(filter.PageSize);
                        sbSQL.Append(" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.SongListId) as rownumber,A.SongListId,A.SongListName,A.SongListDesc,A.SongListCCount,A.SongListTime,A.SongListImg,A.SongListMusic,A.SongListAuthor,B.AdminName  from North_Songs A left join North_Admin B on A.SongListAuthor=B.AdminId where  1 = 1  ");
                        if (!string.IsNullOrEmpty(filter.name))
                        {
                            sbSQL.Append(" and B.SongListName like '%' + @SongListName + '%'");
                            SqlParameter param = new SqlParameter("@SongListName", SqlDbType.NVarChar);
                            param.Value = filter.name;
                            listParam.Add(param);
                        }
                        sbSQL.Append(") WTX where rownumber > (");
                        sbSQL.Append((filter.CurrentPage - 1) * filter.PageSize);
                        sbSQL.Append(")");
                        using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                        {
                            while (dr.Read())
                            {
                                int iIndex = 0;
                                SongHelper list = new SongHelper();
                                list.Songs= new ModelsHelper().GetSongsModels(dr);
                                list.Admin= new ModelsHelper().GetAdminModels(dr);
                                Lists.Add(list);
                                if (iIndex == 0)
                                {
                                    total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                                }
                                iIndex++;
                            }
                        }

                    }

                }

            }
            catch (Exception ex)
            {

            }
            return Lists;
        }

        public int ModifySongList(North_Songs songs)
        {
            int bResult = 0;
            try
            {
                string sql = $"update North_Songs set SongListName='{songs.SongListName}',SongListDesc='{songs.SongListDesc}',SongListTime='{songs.SongListTime}',SongListImg='{songs.SongListImg}',SongListMusic='{songs.SongListMusic}',SongListAuthor='{songs.SongListAuthor}' where SongListId='{songs.SongListId}'";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception ex)
            {
            }
            return bResult;
        }
        public int AddSongList(North_Songs songs)
        {
            int bResult = 0;
            try
            {
                string sql = $"insert into North_Songs (SongListName,SongListDesc,SongListTime,SongListImg,SongListMusic,SongListAuthor) values('{songs.SongListName}','{songs.SongListDesc}','{songs.SongListTime}','{songs.SongListImg}','{songs.SongListMusic}','{songs.SongListAuthor}')";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);

            }
            catch (Exception ex)
            {
            }
            return bResult;
        }
        public int RemoveSongList(int id)
        {
            int bResult = 0;
            try
            {
                string sql = $"delete from North_Songs where SongListId='{id}'";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception ex)
            {
            }
            return bResult;
        }
    }
}
